<?php

/*
 * 项目调试小工具通用头文件
 * @version 1.0 2017-9-20 SoChishun Added.
 * @version 2.0 2017-12-19 SoChishun 重构, 把通用类和方法剥离到debug_head.php头文件
 * @version 2.1 2018-1-12 SoChishun 引入ptshead.php头文件,简化代码,提高代码复用
 * @version 2.2 2018-2-10 sochishun 优化重构配置文件内容: $config新增config节点,增加extra-file外部配置文件功能
 */

// 引入头文件
include './pts_head.php';
// #JWTLite配置位置

// 页面访问验证配置
JWTLite::check_login();
list($loginid, $token, $loginexp) = array_values(JWTLite::$LOGIN_RESULT);

// 数据库配置
$config = [
    'server' => [
        /* 生产环境数据库服务器-production */
        'production' => [
            'type' => 'mysql',
            'host' => '',
            'user' => '',
            'password' => '',
            'database' => '',
            'port' => 3306,
            'charset' => 'utf8',
            'table_prefix' => 't_', // 表名前缀,用于侧边栏过滤表名前缀
            'config' => [
                'is_prod' => false, // 是否正式生产环境,用于在界面上警示提醒
                'extra_file' => ['path' => '../public/db_config.php', 'map' => ['host' => 'DB_HOST', 'user' => 'DB_USER', 'password' => 'DB_PWD', 'port' => 'DB_PORT', 'database' => 'DB_NAME', 'table_prefix' => 'DB_PREFIX']], // 本地配置文件,path为路径, map为字段映射,如果本地文件存在,则以本地配置文件为主
            ],
        ],
        /* 开发环境数据库服务器-develop */
        'develop' => [
            'type' => 'mysql',
            'host' => '',
            'user' => '',
            'password' => '',
            'database' => '',
            'port' => 3306,
            'charset' => 'utf8',
            'table_prefix' => 't_', // 表名前缀,用于侧边栏过滤表名前缀
            'config' => [
                'is_prod' => false, // 是否正式生产环境,用于在界面上警示提醒
                'rinetd' => ['host' => '45.60.100.16', 'port' => 3366], // 端口映射服务器地址,如果配置该字段，则会解析时会替换host和port字段的值, 如果配置了extra_file项, 则本项忽略, 以外部配置文件为主
                'extra_file' => ['path' => '../public/db_config.php', 'map' => ['host' => 'DB_HOST', 'user' => 'DB_USER', 'password' => 'DB_PWD', 'port' => 'DB_PORT', 'database' => 'DB_NAME', 'table_prefix' => 'DB_PREFIX']], // 本地配置文件,path为路径, map为字段映射,如果本地文件存在,则以本地配置文件为主, 内部配置数据和rinetd项的数据都会被外部文件配置内容覆盖
            ],
        ],
        /* 本地环境数据库服务器 */
        'localhost' => [
            'type' => 'mysql',
            'host' => '127.0.0.1',
            'user' => 'root',
            'password' => 'root',
            'database' => 'db_test',
            'port' => 3306,
            'charset' => 'utf8',
            'table_prefix' => 't_', // 表名前缀,用于侧边栏过滤表名前缀
            'config' => [
                'is_prod' => false, // 是否正式生产环境,用于在界面上警示提醒
            ],
        ],
    ],
    // 全局配置
    'config' => [
        'extra_file' => 'data/myadl_server.php', // 外部配置文件,把本节点内容复制并修改到外部文件即可
        'current_server' => 'develop', // 指定当前活动的数据库服务器
    ],
];

// 数据库配置解析
$server = ServerConfUtil::getCurrentServer($config);
$curServId = $server['current_id']; // 当前服务器ID
// 解析host和port
list($host, $port) = ServerConfUtil::parseHostAndPortFromServer($server);
// 实例化数据库操作类
try {
    $dbutil = new DbUtil($server);
} catch (Exception $ex) {
    header("Content-Type:text/html;charset=GBK");
    echo $ex->getMessage();
    exit;
}

// **********************************************共用类********************************************

/**
 * 数据库通用类
 * @version 2017-8-23 Added.
 * @version 2017-12-15 增加导出方法和解析服务器信息的方法
 */
class DbUtil {

    /**
     * 数据库连接DSN
     * @var type 
     */
    protected $dsn = '';

    /**
     * 数据库连接对象
     * @var type 
     */
    protected $link = null;

    public function __construct(array $dbcfg) {
        $this->dsn = $this->getDSN($dbcfg);
        if (empty($this->dsn)) {
            throw new Exception('数据库配置文件无效!');
        }
        try {
            $this->link = new PDO($this->dsn, $dbcfg['user'], $dbcfg['password']);
        } catch (Exception $ex) {
            throw $ex;
        }
    }

    public function setDb($dbname) {
        $this->link->exec('use ' . $dbname);
    }

    public function getDSN(array $server) {
        // 解析host和port是否有端口转发
        list($host, $port) = ServerConfUtil::parseHostAndPortFromServer($server);
        // 数据库类型
        $dbtype = isset($server['type']) ? $server['type'] : 'mysql';
        switch ($dbtype) {
            case 'mysql':
                $dsnarr = ['host' => $host, 'port' => $port];
                if (isset($server['charset'])) {
                    $dsnarr['charset'] = $server['charset'];
                }
                if (!empty($server['database'])) {
                    $dsnarr['dbname'] = $server['database'];
                }
                $dsn = '';
                foreach ($dsnarr as $name => $value) {
                    $dsn .= $name . '=' . $value . ';';
                }
                $dsn = 'mysql:' . $dsn;
                break;
            default:
                $dsn = '';
                break;
        }
        return $dsn;
    }

    function execute($sqlstmt, $params = []) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            // 这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        if ($stmt->execute()) {
            return $stmt->rowCount();
        } else {
            $err = $stmt->errorInfo();
            throw new Exception($err[2]);
        }
    }

    function query($sqlstmt, $params = [], $fetch_style = PDO::FETCH_ASSOC) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            // 这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        if ($stmt->execute()) {
            $list = $stmt->fetchAll($fetch_style);
            return $list;
        } else {
            $err = $stmt->errorInfo();
            throw new Exception($err[2]);
        }
    }

    /**
     * 批处理执行SQL语句
     * 批处理的指令都认为是execute操作
     * @param array $asqls SQL批处理指令
     * @return boolean
     * @since 1.0 2016-6-15 xcaller Added.
     * @example patchQuery($asql);
     */
    function patchQuery($asqls = array()) {
        $msg = array('空命令');
        if (!$asqls) {
            return $msg;
        }
        if (!is_array($asqls)) {
            $asqls = array($asqls);
        }
        $msg = array();
        // 自动启动事务支持
        $this->link->beginTransaction(); // mysql_query("BEGIN");
        try {
            foreach ($asqls as $sql) {
                if (!trim($sql)) {
                    continue;
                }
                $result = $this->link->query($sql);
                if (false === $result) {
                    // 发生错误自动回滚事务
                    $errorInfo = $this->link->errorInfo(); // [SQLSTATE error code, Driver-specific error code, Driver-specific error message] | mysql_error();
                    $msg[] = $errorInfo[2];
                    $this->link->rollBack(); // mysql_query("ROLLBACK");
                    return false;
                }
            }
            // 提交事务
            $this->link->commit(); // mysql_query("COMMIT"); mysql_query("END");
            return $msg;
        } catch (Exception $ex) {
            // 回滚事务
            $msg[] = $ex->getMessage();
            $this->link->rollBack(); // mysql_query("ROLLBACK"); mysql_query("END");
            return $msg;
        }
    }

    function find($sqlstmt, $params = []) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            // 不能用foreach，参考bindParam陷阱：http://www.laruence.com/2012/10/16/2831.html
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        $stmt->execute();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            return $row;
        }
        return null;
    }

    /**
     * 格式化sql脚本
     * <br />去除表注释、字段注释、多余空格、drop命令
     * @param string $str
     * @param string $break_line
     * @return string
     */
    function formatSqlscript($str, $break_line = '\r') {
        $str = preg_replace("/ comment '[^']*'/", '', $str); // comment '...'
        $str = preg_replace("/ comment='[^']*'/", '', $str); // comment='...'
        $str = preg_replace("/--[^$break_line]+/", '', $str); // -- ...
        $str = preg_replace("/\/\*[^\/]+\//", '', $str); // /*...*/
        $str = preg_replace("/drop[^;]+;/", '', $str); // drop table ...;
        $str = preg_replace('/[\s]+/', ' ', trim($str)); // 多个空格合并成一个
        return $str;
    }

    /**
     * 导出数据库表结构到Markdown文件
     * @param type $curdbname
     * @version 2017-12-15
     */
    public function exportDbSchemaAsMarkdown($curdbname) {
        header("Content-type:application/octet-stream");
        header("Content-Disposition:attachment; filename=myadminlite-$curdbname.txt");
        $list = $this->getTables($curdbname);
        foreach ($list as $row) {
            $objname = $row['TABLE_NAME'];
            $columns = $this->getColumns($curdbname, $objname);
            if ($columns) {
                echo '### ', $objname . PHP_EOL;
                echo '|字段|数据类型|可空|默认值|备注|', PHP_EOL;
                echo '|--|--|--|--|--|', PHP_EOL;
                foreach ($columns as $col) {
                    echo '|', $col['COLUMN_NAME'], '|', $col['COLUMN_TYPE'], '|', $col['IS_NULLABLE'], '|', $col['COLUMN_DEFAULT'], '|', str_replace(['|', PHP_EOL], ['\|', ' '], $col['COLUMN_COMMENT']), '|', PHP_EOL;
                }
                echo PHP_EOL;
            }
        }
        unset($list);
        exit();
    }

    /**
     * 导出查询结果到Markdown文件
     * @param type $curtable
     * @param type $sqlstmt
     * @version 2017-12-15
     */
    public function exportQueryDataAsMarkdown($curtable, $sqlstmt) {
        header("Content-type:application/octet-stream");
        header("Content-Disposition:attachment; filename=myadminlite-" . ($curtable ? $curtable . '-' : '') . date('Ymd-His') . ".txt");
        $list = $this->query($sqlstmt);
        echo '###导出数据 ', date('Ymd-His'), PHP_EOL, PHP_EOL;
        if ($curtable) {
            echo '对象：', $curtable, PHP_EOL;
        }
        echo '数量：', count($list), PHP_EOL;
        echo '命令：', $sqlstmt, PHP_EOL, PHP_EOL;
        if ($list) {
            $i = 0;
            foreach ($list as $row) {
                if ($i < 1) {
                    echo '| ', implode(' | ', array_keys($row)), ' |', PHP_EOL;
                    echo '|';
                    foreach ($row as $value) {
                        echo '--|';
                    }
                    echo PHP_EOL;
                }
                $i++;
                echo '| ', implode(" | ", array_values($row)), ' |', PHP_EOL;
            }
            unset($list);
        } else {
            echo '没有查询到结果!';
        }
        echo PHP_EOL;
        exit();
    }

    /**
     * 导出查询结果到SQL脚本
     * @param type $curdbname
     * @param type $curtable
     * @param type $sqlstmt
     * @version 2017-12-15
     */
    public function exportQueryDataAsSQL($curdbname, $curtable, $sqlstmt) {
        header("Content-type:application/octet-stream");
        header("Content-Disposition:attachment; filename=myadminlite-" . $curtable . '-' . date('Ymd-His') . ".sql");
        $columns = $this->getColumns($curdbname, $curtable); // COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT
        $fields = [];
        if ($columns) {
            foreach ($columns as $row) {
                $fields[$row['COLUMN_NAME']] = $row;
            }
        }
        $list = $this->query($sqlstmt);
        echo '/* ', PHP_EOL, '复制数据脚本', PHP_EOL;
        echo '对象：', $curtable, PHP_EOL;
        echo '数量：', count($list), PHP_EOL;
        echo '时间：', date('Ymd-His'), PHP_EOL;
        echo '命令：', $sqlstmt, PHP_EOL;
        echo '工具：', APPNAME, '-', VERSION, PHP_EOL;
        echo '*/', PHP_EOL, PHP_EOL;
        if ($list) {
            $i = 0;
            $strfields = '';
            $values = [];
            $column = [];
            $numbericTypes = ['int', 'smallint', 'bigint', 'decimal', 'float'];
            foreach ($list as $row) {
                if ($i < 1) {
                    $strfields = implode('`,`', array_keys($row));
                }
                $values = [];
                foreach ($row as $field => $value) {
                    $column = $fields[$field];
                    if (is_null($value)) {
                        $values[] = 'null';
                    } else if (in_array($column['DATA_TYPE'], $numbericTypes)) {
                        $values[] = $value;
                    } else {
                        $values[] = "'$value'";
                    }
                }
                echo 'insert into ', $curtable, ' (`', $strfields, '`) values (', implode(",", $values), ');' . PHP_EOL;
                $i++;
            }
            unset($list);
        } else {
            echo '没有查询到结果!';
        }
        echo PHP_EOL;
        exit();
    }
}

/**
 * 服务器配置通用类
 * @version 2017-12-19 Added.
 */
class ServerConfUtil {

    /**
     * 解析服务器配置中的IP和Port
     * @param array $server
     * @param string $host_ip
     * @return array array('host'=>'','port'=>'')
     * @version 1.0 2017-10-13 SoChishun Added.
     */
    public static function parseHostAndPortFromServer(array $server) {
        $host = $server['host'];
        $port = $server['port'];
        if (isset($server['config']['rinetd'])) {
            $rinetdcfg = $server['config']['rinetd'];
            if (isset($rinetdcfg['host']) && !empty($rinetdcfg['host'])) {
                $host = $rinetdcfg['host'];
                // 返回服务器IP, 参考：SERVER_NAME 和HTTP_HOST的区别 (http://blog.sina.com.cn/s/blog_6d96d3160100q39x.html)
                $host_ip = gethostbyname($_SERVER['SERVER_NAME']);
                // 如果服务器IP和端口映射服务器一样,则自动忽略端口映射配置,提高性能 2017-10-13
                if ($host == $host_ip) {
                    $host = $server['host'];
                } else {
                    if (isset($rinetdcfg['port']) && !empty($rinetdcfg['port'])) {
                        $port = $rinetdcfg['port'];
                    }
                }
            }
        }
        return array($host, $port);
    }

    /**
     * 解析配置中的本地配置文件数据
     * @param array $server
     * @return array
     * @version 2017-12-6 added.
     */
    public static function parseServerFromFile(array $server) {
        // 如果有本地配置文件,则以本地配置文件为主
        $confFileInfo = isset($server['config']['extra_file']['path']) ? $server['config']['extra_file'] : false;
        if ($confFileInfo && file_exists($confFileInfo['path'])) {
            $confContent = require_once $confFileInfo['path'];
            if (is_array($confContent)) {
                if (empty($confFileInfo['map'])) {
                    $confData = $confContent;
                } else {
                    $confMap = $confFileInfo['map'];
                    $confData = [];
                    foreach ($confMap as $key => $value) {
                        if (array_key_exists($value, $confContent)) {
                            $confData[$key] = $confContent[$value];
                            unset($confContent[$value]);
                        }
                    }
                }
                $server = array_merge($server, $confData);
                unset($server['config']['rinetd']);
                unset($server['config']['extra_file']);
            }
        }
        return $server;
    }

    /**
     * 获取配置内容中的当前数据库服务器配置
     * @param type $config
     * return array
     * @version 2017-12-15
     * @version 2018-2-10 新增外部配置文件合并功能
     */
    public static function getCurrentServer($config) {
        if (!empty($config['config']['extra_file']) && is_file($config['config']['extra_file'])) {
            $configextra = include $config['config']['extra_file'];
            if ($configextra) {
                $config = array_merge($config, $configextra);
            }
        }
        $curServId = $config['config']['current_server']; // 当前服务器名称
        if (!$curServId || !isset($config['server'][$curServId])) {
            exit('服务器[' . $curServId . ']不存在!');
        }
        $server = $config['server'][$curServId]; // 当前数据库服务器配置
        // 如果有本地配置文件,则以本地配置文件内容覆盖页面配置
        $server = self::parseServerFromFile($server);
        $server['current_id'] = $curServId;
        return $server;
    }

}

// ===========================共用函数==========================

/**
 * 显示表格
 * <br />示例：show_table($list, array('head'=>['id'=>'编号','name'=>'名称','create_time'=>'日期'],'fn'=> ['create_time' => 'from_unixtime']));
 * @param array $list
 * @param array $option head, fn, is_list
 * @return void
 * @since 2017-10-19
 */
function show_table($list, $option = array()) {
    if (!$list) {
        echo '<div>查无记录!</div>';
        return;
    }
    $default = array(
        'head' => array(), // 头部定义,如 array('id'=>'编号','name'=>'名称')
        'fn' => array(), // 字段函数, 如 array('create_time'=>'from_unixtime')
        'is_list' => true, // 是否列表,即 list或row
        'style' => 'class="grid"'
    );
    $option = $option ? array_merge($default, $option) : $default;
    list($alias_head, $displayfns, $is_list, $style) = array_values($option);

    echo '<table cellpadding="0" cellspacing="0" border="1" ', $style, '>';
    echo '<tr>';
    if ($alias_head) {
        foreach ($alias_head as $field => $text) {
            echo '<th title="', $field, '">', $text, '</th>';
        }
    } else {
        $fields = $is_list ? array_keys(current($list)) : array_keys($list);
        echo '<th>', implode('</th><th>', $fields), '</th>';
    }
    echo'</tr>';
    if ($is_list) {
        // 数组列表
        foreach ($list as $row) {
            echo '<tr>';
            foreach ($row as $field => $val) {
                echo '<td>', ($displayfns && array_key_exists($field, $displayfns) ? call_user_func($displayfns[$field], $val) : $val), '</td>';
            }
            echo '</tr>';
            //echo '<tr><td>', implode('</td><td>', array_values($row)), '</td></tr>'; // 有XSS攻击漏洞
        }
    } else {
        // 数组对象
        echo '<tr>';
        foreach ($list as $field => $val) {
            echo '<td>', ($displayfns && array_key_exists($field, $displayfns) ? call_user_func($displayfns[$field], $val) : $val), '</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

/**
 * 输出单选框组
 * <br />示例：html_radio('type', array('video_id' => '直播ID', 'user_id' => '主播ID'), ($action == 'egg' ? $type : ''))
 * @param string $name
 * @param array $data
 * @param string $checked_value
 * @version 2017-12-19
 */
function html_radio($name, $data, $checked_value) {
    foreach ($data as $value => $text) {
        echo '<input type="radio" name="', $name, '" value="', $value, '" required="required" title="', $value, '"', ($checked_value == $value ? ' checked="checked"' : ''), ' />', $text;
    }
}

/**
 * 格式化时间戳
 * @param int $timestamp
 * @param string $format
 * @return string
 */
function from_unixtime($timestamp, $format = 'Y-m-d H:i:s') {
    return date($format, $timestamp);
}
